{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>目录<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#默认顺序显示\" data-toc-modified-id=\"默认顺序显示-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>默认顺序显示</a></span></li><li><span><a href=\"#指定显示顺序\" data-toc-modified-id=\"指定显示顺序-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>指定显示顺序</a></span></li><li><span><a href=\"#按照多个列排序\" data-toc-modified-id=\"按照多个列排序-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>按照多个列排序</a></span></li><li><span><a href=\"#按照列位置排序\" data-toc-modified-id=\"按照列位置排序-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>按照列位置排序</a></span></li><li><span><a href=\"#指定排序方向\" data-toc-modified-id=\"指定排序方向-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>指定排序方向</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = mysql.connector.connect(host='127.0.0.1',\n",
    "                      user='iroan',\n",
    "                      password='iroanMYS47',\n",
    "                      database = 'practice_sql')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 默认顺序显示\n",
    "按照底层表中出现的顺序显示(但是顺序不固定)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fish bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bird bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>18 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Raggedy Ann</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>King doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Queen doll</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             prod_name\n",
       "0    Fish bean bag toy\n",
       "1    Bird bean bag toy\n",
       "2  Rabbit bean bag toy\n",
       "3    8 inch teddy bear\n",
       "4   12 inch teddy bear\n",
       "5   18 inch teddy bear\n",
       "6          Raggedy Ann\n",
       "7            King doll\n",
       "8           Queen doll"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = 'select prod_name from Products;'\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 指定显示顺序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bird bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fish bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>King doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Queen doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Raggedy Ann</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             prod_name\n",
       "0   12 inch teddy bear\n",
       "1   18 inch teddy bear\n",
       "2    8 inch teddy bear\n",
       "3    Bird bean bag toy\n",
       "4    Fish bean bag toy\n",
       "5            King doll\n",
       "6           Queen doll\n",
       "7  Rabbit bean bag toy\n",
       "8          Raggedy Ann"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = 'select prod_name from Products order by prod_name;'\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fish bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bird bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>18 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Raggedy Ann</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>King doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Queen doll</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             prod_name\n",
       "0    Fish bean bag toy\n",
       "1    Bird bean bag toy\n",
       "2  Rabbit bean bag toy\n",
       "3    8 inch teddy bear\n",
       "4   12 inch teddy bear\n",
       "5   18 inch teddy bear\n",
       "6          Raggedy Ann\n",
       "7            King doll\n",
       "8           Queen doll"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = 'select prod_name from Products order by prod_id;'\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 按照多个列排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BNBG01</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Fish bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BNBG02</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Bird bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BNBG03</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BR01</td>\n",
       "      <td>5.99</td>\n",
       "      <td>8 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BR02</td>\n",
       "      <td>8.99</td>\n",
       "      <td>12 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BR03</td>\n",
       "      <td>11.99</td>\n",
       "      <td>18 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>RGAN01</td>\n",
       "      <td>4.99</td>\n",
       "      <td>Raggedy Ann</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>RYL01</td>\n",
       "      <td>9.49</td>\n",
       "      <td>King doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>RYL02</td>\n",
       "      <td>9.49</td>\n",
       "      <td>Queen doll</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  prod_price            prod_name\n",
       "0  BNBG01        3.49    Fish bean bag toy\n",
       "1  BNBG02        3.49    Bird bean bag toy\n",
       "2  BNBG03        3.49  Rabbit bean bag toy\n",
       "3    BR01        5.99    8 inch teddy bear\n",
       "4    BR02        8.99   12 inch teddy bear\n",
       "5    BR03       11.99   18 inch teddy bear\n",
       "6  RGAN01        4.99          Raggedy Ann\n",
       "7   RYL01        9.49            King doll\n",
       "8   RYL02        9.49           Queen doll"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = 'select prod_id,prod_price,prod_name from Products;'\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BNBG02</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Bird bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BNBG01</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Fish bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BNBG03</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RGAN01</td>\n",
       "      <td>4.99</td>\n",
       "      <td>Raggedy Ann</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BR01</td>\n",
       "      <td>5.99</td>\n",
       "      <td>8 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BR02</td>\n",
       "      <td>8.99</td>\n",
       "      <td>12 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>RYL01</td>\n",
       "      <td>9.49</td>\n",
       "      <td>King doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>RYL02</td>\n",
       "      <td>9.49</td>\n",
       "      <td>Queen doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>BR03</td>\n",
       "      <td>11.99</td>\n",
       "      <td>18 inch teddy bear</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  prod_price            prod_name\n",
       "0  BNBG02        3.49    Bird bean bag toy\n",
       "1  BNBG01        3.49    Fish bean bag toy\n",
       "2  BNBG03        3.49  Rabbit bean bag toy\n",
       "3  RGAN01        4.99          Raggedy Ann\n",
       "4    BR01        5.99    8 inch teddy bear\n",
       "5    BR02        8.99   12 inch teddy bear\n",
       "6   RYL01        9.49            King doll\n",
       "7   RYL02        9.49           Queen doll\n",
       "8    BR03       11.99   18 inch teddy bear"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = 'select prod_id,prod_price,prod_name from Products order by prod_price,prod_name;'\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 按照列位置排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BNBG02</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Bird bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BNBG01</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Fish bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BNBG03</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RGAN01</td>\n",
       "      <td>4.99</td>\n",
       "      <td>Raggedy Ann</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BR01</td>\n",
       "      <td>5.99</td>\n",
       "      <td>8 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BR02</td>\n",
       "      <td>8.99</td>\n",
       "      <td>12 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>RYL01</td>\n",
       "      <td>9.49</td>\n",
       "      <td>King doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>RYL02</td>\n",
       "      <td>9.49</td>\n",
       "      <td>Queen doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>BR03</td>\n",
       "      <td>11.99</td>\n",
       "      <td>18 inch teddy bear</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  prod_price            prod_name\n",
       "0  BNBG02        3.49    Bird bean bag toy\n",
       "1  BNBG01        3.49    Fish bean bag toy\n",
       "2  BNBG03        3.49  Rabbit bean bag toy\n",
       "3  RGAN01        4.99          Raggedy Ann\n",
       "4    BR01        5.99    8 inch teddy bear\n",
       "5    BR02        8.99   12 inch teddy bear\n",
       "6   RYL01        9.49            King doll\n",
       "7   RYL02        9.49           Queen doll\n",
       "8    BR03       11.99   18 inch teddy bear"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = 'select prod_id,prod_price,prod_name from Products order by 2,3;'\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 指定排序方向"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BR03</td>\n",
       "      <td>11.99</td>\n",
       "      <td>18 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>RYL01</td>\n",
       "      <td>9.49</td>\n",
       "      <td>King doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>RYL02</td>\n",
       "      <td>9.49</td>\n",
       "      <td>Queen doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BR02</td>\n",
       "      <td>8.99</td>\n",
       "      <td>12 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BR01</td>\n",
       "      <td>5.99</td>\n",
       "      <td>8 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>RGAN01</td>\n",
       "      <td>4.99</td>\n",
       "      <td>Raggedy Ann</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>BNBG01</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Fish bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>BNBG02</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Bird bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>BNBG03</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  prod_price            prod_name\n",
       "0    BR03       11.99   18 inch teddy bear\n",
       "1   RYL01        9.49            King doll\n",
       "2   RYL02        9.49           Queen doll\n",
       "3    BR02        8.99   12 inch teddy bear\n",
       "4    BR01        5.99    8 inch teddy bear\n",
       "5  RGAN01        4.99          Raggedy Ann\n",
       "6  BNBG01        3.49    Fish bean bag toy\n",
       "7  BNBG02        3.49    Bird bean bag toy\n",
       "8  BNBG03        3.49  Rabbit bean bag toy"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = 'select prod_id,prod_price,prod_name from Products order by 2 desc;'\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BR03</td>\n",
       "      <td>11.99</td>\n",
       "      <td>18 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>RYL01</td>\n",
       "      <td>9.49</td>\n",
       "      <td>King doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>RYL02</td>\n",
       "      <td>9.49</td>\n",
       "      <td>Queen doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BR02</td>\n",
       "      <td>8.99</td>\n",
       "      <td>12 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BR01</td>\n",
       "      <td>5.99</td>\n",
       "      <td>8 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>RGAN01</td>\n",
       "      <td>4.99</td>\n",
       "      <td>Raggedy Ann</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>BNBG02</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Bird bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>BNBG01</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Fish bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>BNBG03</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  prod_price            prod_name\n",
       "0    BR03       11.99   18 inch teddy bear\n",
       "1   RYL01        9.49            King doll\n",
       "2   RYL02        9.49           Queen doll\n",
       "3    BR02        8.99   12 inch teddy bear\n",
       "4    BR01        5.99    8 inch teddy bear\n",
       "5  RGAN01        4.99          Raggedy Ann\n",
       "6  BNBG02        3.49    Bird bean bag toy\n",
       "7  BNBG01        3.49    Fish bean bag toy\n",
       "8  BNBG03        3.49  Rabbit bean bag toy"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = 'select prod_id,prod_price,prod_name from Products order by prod_price desc, prod_name;'\n",
    "pd.read_sql_query(statement,conn)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "base"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {
    "height": "12px",
    "width": "163px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "目录",
   "title_sidebar": "大纲",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
